const { CategoryVo: CategoryModel } = require("@/model/Category");
const category = {
  async GetOption() {
    let res = await CategoryModel.findAll({
      attributes: ["id", "name"],
    });
    return res.map((item) => {
      return {
        value: item.dataValues.id,
        label: item.dataValues.name,
      };
    });
  },
  async GetList(data = {}) {
    let query = `
    SELECT c.id, c.name, COUNT(at.id) AS article_count, c.created_at, c.updated_at
    FROM category c
    LEFT JOIN article at ON c.id = at.category_id AND at.is_delete = 0 AND at.status = 1
  `;

    // 如果有关键字，则添加过滤条件
    if (data.keyword) {
      //筛选标签的关键字
      query += ` WHERE c.name LIKE '%${data.keyword}%'`;
    }

    // 添加分组
    query += " GROUP BY c.id ";
    query += " ORDER BY c.id DESC ";
    const [rows] = await global.mysqlPool.execute(
      `SELECT COUNT(*) as count FROM category c`
    );
    const total = rows[0].count; //统计的数量

    if (Reflect.ownKeys(data).length > 0) {
      query += ` LIMIT ${data.page_size} OFFSET ${
        (data.page_num - 1) * data.page_size
      } `;
    }

    const [results] = await global.mysqlPool.execute(query);

    return {
      datas: results,
      total,
    };
  },
};

module.exports = category;
